package dnl.persistence;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateToolz {

	private JdbcTemplate jdbcTemplate;

	public JdbcTemplateToolz(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public boolean tableExists(String tableName) throws SQLException {
		Set<String> existingTableNames = getExistingTableNames(false, true);
		return existingTableNames.contains(tableName.toLowerCase());
	}

    public Set<String> getExistingTableNames() throws SQLException {
        return getExistingTableNames(true, true);
    }

	public Set<String> getExistingTableNames(boolean full, boolean lowerCase) throws SQLException {
		Set<String> tableNames = new HashSet<>();
		DatabaseMetaData metaData = jdbcTemplate.getDataSource().getConnection().getMetaData();
		ResultSet res = metaData.getTables(null, null, null, new String[] { "TABLE" });
		while (res.next()) {
			String schema = res.getString("TABLE_SCHEM");
			String tableName = res.getString("TABLE_NAME");
			if (full) {
                final String t = schema + "." + tableName;
                tableNames.add(lowerCase? t.toLowerCase() : t);
			} else {
				tableNames.add(lowerCase ? tableName.toLowerCase() : tableName);
			}
		}
		return tableNames;
	}

	public Set<String> getColumnNames(String tableName) throws SQLException {
		Set<String> columnNames = new HashSet<>();
		DatabaseMetaData metaData = jdbcTemplate.getDataSource().getConnection().getMetaData();
		ResultSet res = metaData.getColumns(null, null, tableName, null);
		while (res.next()) {
			String n = res.getString(1);
			columnNames.add(n);
		}
		return columnNames;
	}

	public void createTable(TableInfo tableInfo) throws SQLException {
		StringBuilder sb = new StringBuilder("CREATE TABLE ");
		sb.append(tableInfo.getTableName());
		sb.append("(\n");
		ColumnInfo[] columnInfos = tableInfo.getColumnInfosAsArray();
		for (int i = 0; i < columnInfos.length; i++) {
			ColumnInfo columnInfo = columnInfos[i];
			sb.append(columnInfo.getColumnName());
			sb.append(" ");
			sb.append(columnInfo.getSqlType().name());
			if(columnInfo.hasSize()){
				sb.append("(");
				sb.append(columnInfo.getSize());
				sb.append(")");
			}
			if( i < columnInfos.length-1){
				sb.append(",\n");
			}
		}
		sb.append(")");
        executeDdl(sb.toString());
	}

	public void executeDdl(String ddl) throws SQLException {
		jdbcTemplate.execute(ddl);
	}
}
